﻿using Excel2DbTool.Helpers;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace Excel2DbTool.Models
{
    interface IOracleSQL
    {
        String ToInsertStr();
    }

    interface ISQLServerSQL
    {
        String ToInsertStr();
    }

    interface IMySQLSQL
    {
        String ToInsertStr();
    }


    public class ExcelRowRecord : IOracleSQL, ISQLServerSQL, IMySQLSQL
    {
        protected List<Cell> Cells;

        public ExcelRowRecord()
        {
            Cells = new List<Cell>(150);
        }

        public void AddCell(Cell cell)
        {
            Cells.Add(cell);
        }

        public Cell this[int index]
        {
            get
            {
                return Cells[index];
            }
        }


        public String FieldNameStr
        {
            get
            {
                List<String> lstFieldName = Cells.Select(cell => cell.FieldName).ToList();
                return String.Join(",", lstFieldName);
            }
        }

        public String FieldValStr
        {
            get
            {
                List<String> lstFieldVal = Cells.Select(cell => cell.Value.ToString()).ToList();
                return String.Join(",", lstFieldVal);
            }
        }

        /// <summary>
        /// 形如: val1,val2,val3....用于向数据库中插入值
        /// </summary>
        public override string ToString()
        {
            List<String> lst = Cells.Select(cell => cell.Value.ToString()).ToList();
            return String.Join(",", lst);
        }


        string IOracleSQL.ToInsertStr()
        {
            StringBuilder sb = new StringBuilder(1000);
            sb.AppendFormat("INSERT INTO {0} ", ConfigHelper.TableName);
            sb.AppendFormat("({0}) VALUES ", FieldNameStr);
            sb.AppendFormat("({0})", FieldValStr);
            return sb.ToString();
        }

        string ISQLServerSQL.ToInsertStr()
        {
            StringBuilder sb = new StringBuilder(1000);
            sb.AppendFormat("INSERT INTO {0} ", ConfigHelper.TableName);
            List<String> lstFieldName = Cells.Select(cell => cell.FieldName).ToList();
            sb.AppendFormat("({0}) VALUES ", String.Join(",", lstFieldName));
            List<String> lstFieldVal = Cells.Select(cell => cell.Value.ToString()).ToList();
            sb.AppendFormat("({0})", String.Join(",", lstFieldVal));

            return sb.ToString();
        }

        string IMySQLSQL.ToInsertStr()
        {
            StringBuilder sb = new StringBuilder(1000);
            throw new NotImplementedException();
        }
    }
}
